import sys
print(sys.executable) # /opt/homebrew/opt/python@3.10/bin/python3.10/Users/noru/opt/anaconda3/envs/analysis_python_3.10/bin/python
import sys
print(sys.executable) # /opt/homebrew/opt/python@3.10/bin/python3.10/Users/noru/opt/anaconda3/envs/analysis_python_3.10/bin/python
print(sys.path)#
#sys.path.remove('/opt/homebrew/lib/python3.10/site-packages')
# sys.path.append('/opt/homebrew/lib/python3.10/site-packages')['/Users/noru/Documents/analysis/udong_test', '/Users/noru/opt/anaconda3/envs/analysis_python_3.10/lib/python310.zip', '/Users/noru/opt/anaconda3/envs/analysis_python_3.10/lib/python3.10', '/Users/noru/opt/anaconda3/envs/analysis_python_3.10/lib/python3.10/lib-dynload', '', '/Users/noru/opt/anaconda3/envs/analysis_python_3.10/lib/python3.10/site-packages']
import datetime
from datetime import datetime
import time
import pandas as pd
from pymongo import MongoClient
from google.cloud import bigquery
from google.oauth2 import service_account
import matplotlib.pyplot as plt
import folium
cd = service_account.Credentials.from_service_account_file(
'/Users/noru/Documents/google_cloud_composer/administrator_key/woodong-default_compute_service_acount/woodong09-prod-d83693476956.json',
)
bqclient = bigquery.Client(credentials=cd, project='woodong09-prod')
## << DEV >> ##
# mongoClient = MongoClient("mongodb+srv://app:lLWoWVRoZuw6z1Qt@woodong09-dev.z9hwj.mongodb.net/?retryWrites=true&w=majority")
## << LIVE >> ##
connection = MongoClient("mongodb+srv://app:lLWoWVRoZuw6z1Qt@woodong09-prod.jhv7l.mongodb.net/?retryWrites=true&w=majority")
mongoDb = connection.market
ordersCollection = mongoDb.orders# 날짜 설정
dateTime = datetime(2022, 11, 5, 9, 0, 0) # 22/11/5(토요일) UTC 기준
# unix timestamp (milliseconds) 설정
unixTimestamp = int(datetime.timestamp(dateTime)*1000)
# convert from [unix timestamp (milliseconds)] to [datetime]
convertDateTime = datetime.fromtimestamp(unixTimestamp/1000).strftime('%Y-%m-%d %H:%M:%S.%f')
print(dateTime)
print(unixTimestamp)
print(convertDateTime)2022-11-05 09:00:00
1667606400000
2022-11-05 09:00:00.000000
pipeline = [
{
"$match": {"order_time":{"$lt": unixTimestamp}}
},
{
"$project": {"_id":1, "fbuid":1,
"entry_route":1,
"original_product_id":1, "product_id":1, "title": 1,
"seller_id":1,
"createdAt":1, "order_time":1,
"payment.method":1, "payment.amount":1, "payment.status":1,
"delivery_method":1,
"pickup":1
}
},
]
ordersDocs = ordersCollection.aggregate(pipeline)
ordersDocsList = []
for doc in ordersDocs:
# 기본 값을 포함
_id = str(doc.get("_id", None))
fbuid = str(doc.get("fbuid", None))
entry_route = str(doc.get("entry_route", None))
original_product_id = str(doc.get("original_product_id", None))
product_id = str(doc.get("product_id", None))
title = str(doc.get("title", None))
seller_id = str(doc.get("seller_id", None))
#createdAt = datetime.strptime(str(doc.get("createdAt", None))[:19], "%Y-%m-%d %H:%M:%S")
created_utc = str(doc.get("createdAt", None))[:19]
order_time = int(doc.get("order_time", None))
payment_method = str(doc.get("payment", None).get("method", None))
payment_amount = int(doc.get("payment", None).get("amount", 0))
payment_status = str(doc.get("payment", None).get("status", None))
delivery_method = str(doc.get("delivery_method", None))
if doc.get("pickup", None) == None:
pickup_id = None
elif doc.get("pickup", None) != None :
pickup_id = str(doc.get("pickup", None).get("id", None))
if doc.get("pickup", None) == None :
pickup_lat = None
elif doc.get("pickup", None).get("location", None) == None :
pickup_lat = None
elif doc.get("pickup", None).get("location", None).get("lat", None) == None :
pickup_lat = None
elif doc.get("pickup", None).get("location", None).get("lat", None) != None :
pickup_lat = str(doc.get("pickup", None).get("location", None).get("lat", None))
if doc.get("pickup", None) == None :
pickup_long = None
elif doc.get("pickup", None).get("location", None) == None :
pickup_long = None
elif doc.get("pickup", None).get("location", None).get("long", None) == None :
pickup_long = None
elif doc.get("pickup", None).get("location", None).get("long", None) != None :
pickup_long = str(doc.get("pickup", None).get("location", None).get("long", None))
if doc.get("pickup", None) == None :
pickup_region_code = None
elif doc.get("pickup", None).get("location", None) == None :
pickup_region_code = None
elif doc.get("pickup", None).get("location", None).get("region_code", None) == None :
pickup_region_code = None
elif doc.get("pickup", None).get("location", None).get("region_code", None) != None :
pickup_region_code = str(doc.get("pickup", None).get("location", None).get("region_code", None))
if doc.get("pickup", None) == None :
pickup_region_name = None
elif doc.get("pickup", None).get("location", None) == None :
pickup_region_name = None
elif doc.get("pickup", None).get("location", None).get("region_name", None) == None :
pickup_region_name = None
elif doc.get("pickup", None).get("location", None).get("region_name", None) != None :
pickup_region_name = str(doc.get("pickup", None).get("location", None).get("region_name", None))
if doc.get("pickup", None) == None :
pickup_title = None
elif doc.get("pickup", None).get("location", None) == None :
pickup_title = None
elif doc.get("pickup", None).get("location", None).get("title", None) == None :
pickup_title = None
elif doc.get("pickup", None).get("location", None).get("title", None) != None :
pickup_title = str(doc.get("pickup", None).get("location", None).get("title", None))
if doc.get("pickup", None) == None :
pickup_direct_shop = None
elif doc.get("pickup", None).get("location", None) == None :
pickup_direct_shop = None
elif doc.get("pickup", None).get("location", None).get("direct_shop", None) == None :
pickup_direct_shop = None
elif doc.get("pickup", None).get("location", None).get("direct_shop", None) != None :
pickup_direct_shop = str(doc.get("pickup", None).get("location", None).get("direct_shop", None))
ordersDocsList.append({"order_id": _id, "fbuid": fbuid,
"entry_route": entry_route,
"original_product_id": original_product_id, "product_id": product_id, "title": title,
"seller_id": seller_id,
"created_utc": created_utc, "order_time": order_time,
"payment_method": payment_method, "payment_amount": payment_amount, "payment_status": payment_status,
"delivery_method": delivery_method,
"pickup_id": pickup_id, "pickup_lat": pickup_lat, "pickup_long": pickup_long,
"pickup_region_code": pickup_region_code, "pickup_region_name": pickup_region_name,
"pickup_shop": pickup_title, "is_direct_shop": pickup_direct_shop,
})
ordersDf = pd.DataFrame(ordersDocsList)ordersDf.tail(2)| order_id | fbuid | entry_route | original_product_id | product_id | title | seller_id | created_utc | order_time | payment_method | payment_amount | payment_status | delivery_method | pickup_id | pickup_lat | pickup_long | pickup_region_code | pickup_region_name | pickup_shop | is_direct_shop | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 74359 | 97c88926c7dc | naver:30936932 | HOME | None | ddd15f09-ff47-4e5a-b9ba-ab60d65af5cd | 너무맛잇는츄러스 리뷰만900개이상! 정상가11900원 공구가9900원!! | naver:198406276 | 2022-11-04 14:18:34 | 1667571514434 | credit_debit_card | 23300 | completed | delivery | None | None | None | None | None | None | None |
| 74360 | 9602e2284ec7 | kakao:2223442699 | HOME | None | 0c87f74f-03e9-4ab7-987c-7b9db26b78d3 | 우리동네 소한마리🐂 - 한우 꽃등심 300g | naver:199407471 | 2022-11-04 14:24:44 | 1667571884306 | credit_debit_card | 50000 | completed | pickup | 6b6b5340-c6cd-4ddb-b6b3-33c3ebd54b6a | 37.3176661631366 | 127.08695643701 | 4146552000 | 경기도 용인시 수지구 풍덕천동 504-10 | 풍덕천점 | True |
hjahaha 이게뭐지?
# connection = MongoClient("mongodb+srv://app:lLWoWVRoZuw6z1Qt@woodong09-prod.jhv7l.mongodb.net/?retryWrites=true&w=majority")
mongoDb = connection.region
storesCollection = mongoDb.stores
storesDocs = storesCollection.find()
storesDocsList = []
for doc in storesDocs:
# 기본 값을 포함
_id = str(doc.get("_id", None))
h_code = str(doc.get("h_code", None))
b_code = str(doc.get("b_code", None))
name = str(doc.get("name", None))
if doc.get("geo", None) == None :
coordinates = None
elif doc.get("geo", None).get("coordinates", None) == None :
coordinates = None
elif doc.get("geo", None).get("coordinates", None) != None :
coordinates = doc.get("geo", None).get("coordinates", None)
if doc.get("geo", None) == None :
coordinates_long = None
elif doc.get("geo", None).get("coordinates", None) == None :
coordinates_long = None
elif doc.get("geo", None).get("coordinates", None) != None :
coordinates_long = doc.get("geo", None).get("coordinates", None)[0]
if doc.get("geo", None) == None :
coordinates_lat = None
elif doc.get("geo", None).get("coordinates", None) == None :
coordinates_lat = None
elif doc.get("geo", None).get("coordinates", None) != None :
coordinates_lat = doc.get("geo", None).get("coordinates", None)[1]
storesDocsList.append({"_id": _id,
"pickup_region_code": h_code, "b_code": b_code,
"name": name,
"coordinates": coordinates,
"pickup_long": coordinates_long,
"pickup_lat": coordinates_lat,
})
storesDf = pd.DataFrame(storesDocsList)storesDf.head(3)| _id | pickup_region_code | b_code | name | coordinates | pickup_long | pickup_lat | |
|---|---|---|---|---|---|---|---|
| 0 | 61b6ef1db06458d148378cc6 | 4146552000 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 1 | 61b6ef86b06458d148378cc8 | 4113557000 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 2 | 61b6efe1b06458d148378cce | 4146554000 | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
# connection = MongoClient("mongodb+srv://app:lLWoWVRoZuw6z1Qt@woodong09-prod.jhv7l.mongodb.net/?retryWrites=true&w=majority")
mongoDb = connection.user
myinfosCollection = mongoDb.user.myinfos
dateTime = datetime(2022, 11, 5, 9, 0, 0) # 22/11/5(토요일) UTC 기준
pipeline = [
{
"$match": {"createdAt":{"$lt": dateTime}}
},
{
"$project": {"fbuid":1,
"nickname": 1,
"createdAt":1,
"locationDistance":1,
"preferredRegion":1,
}
},
{
"$sort": {"createdAt": -1}
},
]
myinfosDocs = myinfosCollection.aggregate(pipeline)
myinfosDocsList = []
for doc in myinfosDocs:
# 기본 값을 포함
fbuid = str(doc.get("fbuid", None))
nickname = str(doc.get("nickname", None))
createdAt = str(doc.get("createdAt", None))
locationDistance = int(doc.get("locationDistance", 0))
if doc.get("preferredRegion", None) == None :
coordinates_long = None
elif doc.get("preferredRegion", None).get("x", None) == None :
coordinates_long = None
elif doc.get("preferredRegion", None).get("x", None) != None :
coordinates_long = doc.get("preferredRegion", None).get("x", None)
if doc.get("preferredRegion", None) == None :
coordinates_lat = None
elif doc.get("preferredRegion", None).get("y", None) == None :
coordinates_lat = None
elif doc.get("preferredRegion", None).get("y", None) != None :
coordinates_lat = doc.get("preferredRegion", None).get("y", None)
myinfosDocsList.append({"user_id": fbuid,
"created_at": createdAt,
"nickname": nickname,
"locationDistance": locationDistance,
"coordinates_long": coordinates_long,
"coordinates_lat": coordinates_lat,
})
myinfosDf = pd.DataFrame(myinfosDocsList)myinfosDf.head(3)| user_id | created_at | nickname | locationDistance | coordinates_long | coordinates_lat | |
|---|---|---|---|---|---|---|
| 0 | kakao:2512739756 | 2022-11-05 08:32:39.285000 | 김팀장 | 3 | 126.948135 | 37.489543 |
| 1 | naver:18825577 | 2022-11-05 08:25:39.085000 | 나비잡자 | 3 | 127.088757 | 37.319480 |
| 2 | kakao:2512725134 | 2022-11-05 08:22:55.531000 | 겨울사랑 | 3 | 127.110457 | 37.170548 |
ordersDf.shape(74361, 20)
ordersDf.info()<class 'pandas.core.frame.DataFrame'>
RangeIndex: 74361 entries, 0 to 74360
Data columns (total 20 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 order_id 74361 non-null object
1 fbuid 74361 non-null object
2 entry_route 74361 non-null object
3 original_product_id 74361 non-null object
4 product_id 74361 non-null object
5 title 74361 non-null object
6 seller_id 74361 non-null object
7 created_utc 74361 non-null object
8 order_time 74361 non-null int64
9 payment_method 74361 non-null object
10 payment_amount 74361 non-null int64
11 payment_status 74361 non-null object
12 delivery_method 74361 non-null object
13 pickup_id 73241 non-null object
14 pickup_lat 42029 non-null object
15 pickup_long 42029 non-null object
16 pickup_region_code 42429 non-null object
17 pickup_region_name 42429 non-null object
18 pickup_shop 42429 non-null object
19 is_direct_shop 28838 non-null object
dtypes: int64(2), object(18)
memory usage: 11.3+ MB
ordersDf['payment_amount'].describe()count 7.436100e+04
mean 1.829529e+04
std 1.972587e+04
min 0.000000e+00
25% 1.000000e+04
50% 1.550000e+04
75% 2.100000e+04
max 1.791000e+06
Name: payment_amount, dtype: float64
## < 픽업상품 구매유저의 지역 데이터 >
# 구매유저 fbuid - 중복 제외
pickupPu = ordersDf[ordersDf['delivery_method'] == 'pickup'].drop_duplicates(['fbuid'], ignore_index = True)
pickupPu = pd.merge(pickupPu['fbuid'],myinfosDf, how='left', left_on='fbuid', right_on='user_id')
pickupPu = pickupPu.dropna(axis=0)
pickupPu.head(3)| fbuid | user_id | created_at | nickname | locationDistance | coordinates_long | coordinates_lat | |
|---|---|---|---|---|---|---|---|
| 0 | kakao:1839010893 | kakao:1839010893 | 2021-08-06 13:22:24.106000 | 쩡이삐니 | 0.0 | 127.058896 | 37.209882 |
| 1 | kakao:1824017425 | kakao:1824017425 | 2021-07-27 07:41:01.754000 | 픽미픽미아이스현미마을 | 0.0 | 127.040779 | 37.188293 |
| 2 | naver:19786798 | naver:19786798 | 2021-07-30 13:53:24.547000 | 쁘띠프루츠용인수원 | 0.0 | 127.114018 | 37.331035 |
## < 픽업상품 구매유저의 지역 데이터 >
# 구매유저 fbuid - 중복 제외
deliveryPu = ordersDf[ordersDf['delivery_method'] == 'delivery'].drop_duplicates(['fbuid'], ignore_index = True)
deliveryPu = pd.merge(deliveryPu['fbuid'],myinfosDf, how='left', left_on='fbuid', right_on='user_id')
deliveryPu = deliveryPu.dropna(axis=0)
deliveryPu.head(3)| fbuid | user_id | created_at | nickname | locationDistance | coordinates_long | coordinates_lat | |
|---|---|---|---|---|---|---|---|
| 0 | naver:17838899 | naver:17838899 | 2021-08-26 17:18:28.139000 | cmmyeon | 0.0 | 127.071916 | 37.206523 |
| 1 | naver:206973907 | naver:206973907 | 2021-08-03 08:48:28.461000 | 동탄맛집러 | 0.0 | 127.088757 | 37.319480 |
| 2 | naver:38108207 | naver:38108207 | 2021-08-03 03:31:49.373000 | 비치콤보 | 0.0 | 127.088757 | 37.319480 |
ordersDf.head(2)| order_id | fbuid | entry_route | original_product_id | product_id | title | seller_id | created_utc | order_time | payment_method | payment_amount | payment_status | delivery_method | pickup_id | pickup_lat | pickup_long | pickup_region_code | pickup_region_name | pickup_shop | is_direct_shop | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1627620368257_naver:23946297 | naver:23946297 | None | None | naver:1994074711627614920119 | 남동공단떡볶이 | naver:199407471 | 2021-07-30 04:46:09 | 1627620369565 | credit_debit_card | 6500 | completed | None | 572243dc-5748-4304-9e98-7c255ebd2aa1 | None | None | None | None | None | None |
| 1 | 1627620558367_kakao:1827574818 | kakao:1827574818 | None | None | naver:1994074711627614920119 | 남동공단떡볶이 | naver:199407471 | 2021-07-30 04:49:17 | 1627620557489 | credit_debit_card | 6500 | completed | None | 572243dc-5748-4304-9e98-7c255ebd2aa1 | None | None | None | None | None | None |
# shopSales = ordersDf[ordersDf['payment_status'] == 'completed']
# shopSales['pickup_region_code']
# shopSales = shopSales[shopSales['pickup_region_code'] != None] << 작업 실패..
# shopSales.sort_values(by=['created_utc'], ascending=True).head(2)shopSales = ordersDf[ordersDf['payment_status'] == 'completed']
ordersCreatedAtUTC = pd.to_datetime(shopSales['created_utc'])
shopSales['create_datetime_kst'] = ordersCreatedAtUTC.dt.tz_localize('UTC').dt.tz_convert('Asia/Seoul')
shopSales['created_yearmonth_kst'] = pd.to_datetime(shopSales['create_datetime_kst']).dt.strftime('%Y-%m')
shopSales = shopSales.groupby(['created_yearmonth_kst', 'pickup_region_code'])['payment_amount'].sum()
shopSales = shopSales.reset_index() # groupby 결과를 그대로 데이터프레임으로 저장
shopSales = shopSales.sort_values(by=['created_yearmonth_kst', 'pickup_region_code'], ascending=False)
shopSales = pd.merge(left = shopSales, right = storesDf, how = "left", on = "pickup_region_code")
shopSales = shopSales.dropna(axis=0)
shopSales
# shopSales#[['created_yearmonth_kst', 'pickup_region_code', 'name', 'payment_amount']]
# .plot(kind='barh', color='royalblue')
# plt.title('매장별 누적 매출')
# plt.show()/var/folders/0l/flwlyzy53j95w831rpmyv9yw0000gn/T/ipykernel_15277/4111126378.py:3: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead
See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
/var/folders/0l/flwlyzy53j95w831rpmyv9yw0000gn/T/ipykernel_15277/4111126378.py:4: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead
See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
| created_yearmonth_kst | pickup_region_code | payment_amount | _id | b_code | name | coordinates | pickup_long | pickup_lat | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 2022-11 | 4159061000 | 2049200 | 62a81ab567a8fa772735f34a | 4159013400 | 동탄 목동점 | [127.124116387163, 37.1827023571775] | 127.124116 | 37.182702 |
| 1 | 2022-11 | 4159058500 | 2335200 | 62a81a4467a8fa772735f349 | 4159012700 | 동탄 푸른점 | [127.059686888308, 37.2017522037433] | 127.059687 | 37.201752 |
| 2 | 2022-11 | 4146554000 | 2072000 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 3 | 2022-11 | 4146552000 | 9803600 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 4 | 2022-11 | 4113557000 | 3875100 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 5 | 2022-10 | 4159061000 | 8679800 | 62a81ab567a8fa772735f34a | 4159013400 | 동탄 목동점 | [127.124116387163, 37.1827023571775] | 127.124116 | 37.182702 |
| 6 | 2022-10 | 4159058500 | 10057700 | 62a81a4467a8fa772735f349 | 4159012700 | 동탄 푸른점 | [127.059686888308, 37.2017522037433] | 127.059687 | 37.201752 |
| 7 | 2022-10 | 4146554000 | 7381100 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 8 | 2022-10 | 4146552000 | 40242600 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 9 | 2022-10 | 4113557000 | 15931300 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 10 | 2022-09 | 4159061000 | 7324800 | 62a81ab567a8fa772735f34a | 4159013400 | 동탄 목동점 | [127.124116387163, 37.1827023571775] | 127.124116 | 37.182702 |
| 11 | 2022-09 | 4159058500 | 8530000 | 62a81a4467a8fa772735f349 | 4159012700 | 동탄 푸른점 | [127.059686888308, 37.2017522037433] | 127.059687 | 37.201752 |
| 12 | 2022-09 | 4146554000 | 6415900 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 13 | 2022-09 | 4146552000 | 35004100 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 14 | 2022-09 | 4113557000 | 15185600 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 15 | 2022-08 | 4159061000 | 8389900 | 62a81ab567a8fa772735f34a | 4159013400 | 동탄 목동점 | [127.124116387163, 37.1827023571775] | 127.124116 | 37.182702 |
| 16 | 2022-08 | 4159058500 | 10522700 | 62a81a4467a8fa772735f349 | 4159012700 | 동탄 푸른점 | [127.059686888308, 37.2017522037433] | 127.059687 | 37.201752 |
| 17 | 2022-08 | 4146554000 | 9654100 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 18 | 2022-08 | 4146552000 | 47840100 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 19 | 2022-08 | 4113557000 | 17965700 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 20 | 2022-07 | 4159061000 | 7366200 | 62a81ab567a8fa772735f34a | 4159013400 | 동탄 목동점 | [127.124116387163, 37.1827023571775] | 127.124116 | 37.182702 |
| 21 | 2022-07 | 4159058500 | 8326900 | 62a81a4467a8fa772735f349 | 4159012700 | 동탄 푸른점 | [127.059686888308, 37.2017522037433] | 127.059687 | 37.201752 |
| 22 | 2022-07 | 4146554000 | 10319700 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 23 | 2022-07 | 4146552000 | 50462800 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 24 | 2022-07 | 4113557000 | 18268100 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 25 | 2022-06 | 4159061000 | 2384700 | 62a81ab567a8fa772735f34a | 4159013400 | 동탄 목동점 | [127.124116387163, 37.1827023571775] | 127.124116 | 37.182702 |
| 26 | 2022-06 | 4159058500 | 2561900 | 62a81a4467a8fa772735f349 | 4159012700 | 동탄 푸른점 | [127.059686888308, 37.2017522037433] | 127.059687 | 37.201752 |
| 27 | 2022-06 | 4146554000 | 9240000 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 28 | 2022-06 | 4146552000 | 38255900 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 30 | 2022-06 | 4113557000 | 14915200 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 31 | 2022-05 | 4146554000 | 10689000 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 32 | 2022-05 | 4146552000 | 42943100 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 33 | 2022-05 | 4113557000 | 17967000 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 34 | 2022-04 | 4146554000 | 11721900 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 35 | 2022-04 | 4146552000 | 47662800 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 36 | 2022-04 | 4113557000 | 18885700 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
| 37 | 2022-03 | 4146554000 | 5050800 | 61b6efe1b06458d148378cce | 4146510200 | 죽전점 | [127.12501304728, 37.3304009693292] | 127.125013 | 37.330401 |
| 38 | 2022-03 | 4146552000 | 21147800 | 61b6ef1db06458d148378cc6 | 4146510100 | 풍덕천점 | [127.08695643701, 37.3176661631366] | 127.086956 | 37.317666 |
| 39 | 2022-03 | 4113557000 | 8247900 | 61b6ef86b06458d148378cc8 | 4113510300 | 정자점 | [127.120136737144, 37.3601992734629] | 127.120137 | 37.360199 |
# pandas 데이터프레임.iplot() 함수 호출 전에 로드해야 함
import chart_studio.plotly as py
import cufflinks as cf
cf.go_offline(connected=True)#cf.help('bar')a = shopSales[['created_yearmonth_kst', 'name', 'payment_amount']].set_index(['created_yearmonth_kst']).loc['2022-03']['name']
list(a)['죽전점', '풍덕천점', '정자점']
# https://plotly.com/python/bar-charts/
import plotly.express as px
long_df = px.data.medals_long()
fig = px.bar(shopSales[['name', 'payment_amount', 'created_yearmonth_kst']], x="created_yearmonth_kst", y="payment_amount", color="name", title="매장별 월매출")
fig.show()shopSales[['payment_amount', 'created_yearmonth_kst']].set_index(['created_yearmonth_kst']).iplot(kind='bar')shopSalesPivot = shopSales[['name', 'payment_amount', 'created_yearmonth_kst']].pivot(index='created_yearmonth_kst', columns='name', values='payment_amount')shopSalesPivot.iplot(kind='bar', barmode='stack')#cf.help('scatter')shopSalesPivot.iplot(kind='scatter')ordersDf.groupby(['pickup_region_code', 'pickup_lat', 'pickup_long']).sum()/var/folders/0l/flwlyzy53j95w831rpmyv9yw0000gn/T/ipykernel_15277/1578443391.py:1: FutureWarning:
The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.
| order_time | payment_amount | |||
|---|---|---|---|---|
| pickup_region_code | pickup_lat | pickup_long | ||
| 4113557000 | 37.3595260841877 | 127.120610882303 | 59511543808874 | 850000 |
| 37.360071243708 | 127.120130663342 | 41355509108988 | 435100 | |
| 37.3601144883473 | 127.120135247444 | 2706508795035858 | 32395300 | |
| 37.3601992734629 | 127.120136737144 | 9221278212090512 | 83820400 | |
| 37.360200986796166 | 127.12013538532256 | 2670696300769442 | 23339500 | |
| 4128158000 | 37.64697646971491 | 126.89495970523106 | 1654758132159 | 1000 |
| 4146351000 | 37.2742130834493 | 127.105503350968 | 24822788838388 | 394600 |
| 4146552000 | 37.1827023571775 | 127.124116387163 | 1660395244976 | 27000 |
| 37.31719334194898 | 127.08677741878975 | 7422033851458611 | 66664300 | |
| 37.3176256820579 | 127.086990009479 | 347014844305478 | 3965000 | |
| 37.3176653507245 | 127.086958466759 | 5733238553183279 | 67125600 | |
| 37.3176661631366 | 127.08695643701 | 24595912376536225 | 223792400 | |
| 4146554000 | 37.3298572055999 | 127.124320586548 | 52882666183769 | 654000 |
| 37.3299403230536 | 127.124108595291 | 3306138206413 | 44000 | |
| 37.3299506737632 | 127.124119218729 | 31399214968150 | 497000 | |
| 37.33030290087551 | 127.12462033288092 | 3136016996224518 | 33241700 | |
| 37.3304009693292 | 127.12501304728 | 4621208723592732 | 42934200 | |
| 4159058500 | 37.2017522037433 | 127.059686888308 | 4915110483465426 | 45720800 |
| 4159059000 | 37.20657779462817 | 127.09831215838679 | 1663208258913 | 10000 |
| 4159061000 | 37.1827023571775 | 127.124116387163 | 4107148811427457 | 39284000 |
userList = myinfosDf[['user_id', 'coordinates_lat', 'coordinates_long']]
userList = userList.dropna(axis=0)
userList.head(2)
# userList.shape| user_id | coordinates_lat | coordinates_long | |
|---|---|---|---|
| 0 | kakao:2512739756 | 37.489543 | 126.948135 |
| 1 | naver:18825577 | 37.319480 | 127.088757 |
from folium.plugins import MarkerCluster
# 위도 (보라 초등학교)
latitude = 37.2639233
# 경도
longitude = 127.1072747
m = folium.Map(
location=[latitude, longitude],
zoom_start=11.2,
# width=750,
# height=500,
# tiles='cartodbpositron'
)
mrkerCluster = MarkerCluster().add_to(m)
# 매장별 반경 3km(3000m) 도식 <- 반경이 고정되지 않아서 안쓴다.
# for lat, long, name in zip(storesDf['pickup_lat'], storesDf['pickup_long'], storesDf['name']):
# folium.CircleMarker([lat, long], popup=name, radius=15, color='#FF580B', fill_color='#FF580B',).add_to(m)
# << 매장 위치 표시 >>
for lat, long, name in zip(storesDf['pickup_lat'], storesDf['pickup_long'], storesDf['name']):
folium.Marker(location = [lat, long], popup=name, icon=folium.Icon(color='red', icon='star')).add_to(m)
# << 가입 유저 - 위치 표시 >>
for lat, long in zip(userList['coordinates_lat'], userList['coordinates_long']):
folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)
# << 픽업상품 구매 유저 - 위치 표시 >>
# for lat, long in zip(pickupPu['coordinates_lat'], pickupPu['coordinates_long']):
# folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)
# << 배송상품 구매 유저 - 위치 표시 >>
# for lat, long in zip(deliveryPu['coordinates_lat'], deliveryPu['coordinates_long']):
# folium.Marker([lat, long], icon = folium.Icon(color="green")).add_to(mrkerCluster)
m